﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Environment;
using Portal.Language;
using Portal.Interface;

namespace Portal.CMS
{
    public class GroupNewsBLL
    {
        public GroupNewsCls CreateModel(string GroupNewsId)
        {
            string Query = "select * from TableGroupNews where GroupNewsId=@GroupNewsId";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                GroupNewsCls GroupNewsCls = new GroupNewsCls();
                GroupNewsCls.GroupNewsId = (string)dtResult.Rows[0]["GroupNewsId"];
                GroupNewsCls.Subject = "";
                try
                {
                    GroupNewsCls.Subject = (string)dtResult.Rows[0]["Subject"];
                }
                catch { }
                GroupNewsCls.belongSite = "";
                try
                {
                    GroupNewsCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                }
                catch { }
                try
                {
                    GroupNewsCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }
                try
                {
                    GroupNewsCls.IsNews = (int)dtResult.Rows[0]["IsNews"];
                }
                catch { }
                return GroupNewsCls;
            }
        }


        public GroupNewsItemCls CreateModelItem(string ActiveLanguage, string GroupNewsItemId)
        {
            string Query = "select * from TableGroupNewsItem where GroupNewsItemId=@GroupNewsItemId";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                GroupNewsItemCls GroupNewsItemCls = new GroupNewsItemCls();
                GroupNewsItemCls.GroupNewsItemId = (string)dtResult.Rows[0]["GroupNewsItemId"];
                GroupNewsItemCls.GroupNewsId = (string)dtResult.Rows[0]["fk_GroupNews_Id"];
                GroupNewsItemCls.GroupNewsItemName = "";
                try
                {
                    GroupNewsItemCls.GroupNewsItemName = (string)dtResult.Rows[0][ActiveLanguage + "GroupNewsItemName"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.GroupNewsItemShortContent = (string)dtResult.Rows[0]["GroupNewsItemShortContent"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.HtmlContent = (string)dtResult.Rows[0]["HtmlContent"];
                }
                catch { }
                GroupNewsItemCls.Description = "";
                try
                {
                    GroupNewsItemCls.Description = (string)dtResult.Rows[0][ActiveLanguage + "gDescription"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.GroupNewsItemAlias = (string)dtResult.Rows[0]["GroupNewsItemAlias"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.GroupNewsItemImage = (string)dtResult.Rows[0]["GroupNewsItemImage"];
                }
                catch { }

                GroupNewsItemCls.IndexOrder = (int)dtResult.Rows[0]["IndexOrder"];
                try
                {
                    GroupNewsItemCls.Visible = (int)dtResult.Rows[0]["Visible"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.IsGroupNews = (int)dtResult.Rows[0]["IsGroupNews"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.AllowRss = (int)dtResult.Rows[0]["AllowRss"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.ParentGroupNewsItemId = (string)dtResult.Rows[0]["fk_ParentGroupNewsItem_Id"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.RssUrl = (string)dtResult.Rows[0]["RssUrl"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.JumpUrl = (string)dtResult.Rows[0]["JumpUrl"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.ShowInMenu = (int)dtResult.Rows[0]["ShowInMenu"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.LevelIndex = (int)dtResult.Rows[0]["LevelIndex"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.ShortSubject = (string)dtResult.Rows[0]["ShortSubject"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.ShowInSubMenu = (int)dtResult.Rows[0]["ShowInSubMenu"];
                }
                catch { }


                return GroupNewsItemCls;
            }
        }

        public DataTable Reading(string belongSite)
        {
            return Reading(Environment.Environments.ConnectionString,belongSite);
        }

        public DataTable Reading(string ConnectionString,string belongSite)
        {
            string Query = "select * from TableGroupNews where belongSite=@belongSite";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar).Value = belongSite;
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForTreeViewNews(string belongSite)
        {
            string Query = "select * from TableGroupNews where belongSite=@belongSite and IsNews=1";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar).Value = belongSite;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingTabHasRunTimeModule()
        {
            string Query = "select * from TableTab where TabId in (select fk_Tab_Id from TableTabModule where LoadType='_runtime')";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingModuleHasRunTime(string TabId)
        {
            string Query = "select TabModuleId,Title from TableTabModule where LoadType='_runtime' and fk_Tab_Id='" + TabId + "'";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingGroupNewsItems(string GroupNewsId, string ParentGroupNewsId)
        {
            string Query = "select * from TableGroupNewsItem where fk_GroupNews_Id=@GroupNewsId";
            if (ParentGroupNewsId != null)
            {
                Query += " and fk_ParentGroupNewsItem_Id='" + ParentGroupNewsId + "' ";
            }
            else
            {
                Query += " and fk_ParentGroupNewsItem_Id is null";
            }

            Query += " order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        
        public DataTable ReadingGroupNewsItemsForMenu(string GroupNewsId, string ParentGroupNewsId)
        {
            string Query = "select * from TableGroupNewsItem where fk_GroupNews_Id=@GroupNewsId and IsGroupNews=1";
            if (ParentGroupNewsId != null)
            {
                Query += " and fk_ParentGroupNewsItem_Id='" + ParentGroupNewsId + "' ";
            }
            else
            {
                Query += " and fk_ParentGroupNewsItem_Id is null";
            }

            Query += " order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingGroupNews()
        {
            string Query = " select * from TableGroupNews where IsNews=1 order by [" + Environments.ActiveLanguage + "Subject]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }



        public DataTable ReadingGroupNewsItemsForSearch(string belongSite)
        {
            string Query = "select * from TableGroupNewsItem where belongSite=@belongSite and GroupNewsItemId in (select fk_GroupNewsItem_Id from TableNews where belongSite=@belongSite) order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingGroupNewsItemsForMove(string GroupNewsId)
        {
            string Query = "select * from TableGroupNewsItem where fk_GroupNews_Id='"+GroupNewsId+"' and  IsGroupNews=1 order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            //sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            //sqlCommand.Parameters["@belongSite"].Value = belongSite;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingNews(string GroupNewsId)
        {
            string Query = "select * from TableNews where fk_GroupNews_Id=@GroupNewsId";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingNewsByGroupNewsItem(string GroupNewsItemId)
        {
            string Query = "select * from TableNews where fk_GroupNewsItem_Id=@GroupNewsItemId and IsPublishing=1 order by EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingNewsForSync(string GroupNewsItemId)
        {
            string Query = "select NewsId from TableNews where fk_GroupNewsItem_Id=@GroupNewsItemId and IsPublishing=1 order by EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string AddGroupNews(
            SqlTransaction sqlTransaction,
            string belongSite,
            string Subject,
            int IsNews)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string GroupNewsId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableGroupNews(GroupNewsId,belongSite,Subject,[" + Environments.ActiveLanguage + "Subject], Owner, IsNews) values (@GroupNewsId,@belongSite,@Subject,@Subject,@Owner,@IsNews)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@Owner", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Owner"].Value = Environments.CurrentLoginUser.LoginName;

            sqlCommand.Parameters.Add("@IsNews", SqlDbType.Int);
            sqlCommand.Parameters["@IsNews"].Value = IsNews;


            try
            {
                sqlCommand.ExecuteNonQuery();
                //doan nay call webservice
                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    DataSet ds = new DataSet("xml");
                //    ds.Tables.Add("GroupNews");
                //    ds.Tables[0].Columns.Add("belongSite");
                //    ds.Tables[0].Columns.Add("ActiveLanguage");
                //    ds.Tables[0].Columns.Add("GroupNewsId");
                //    ds.Tables[0].Columns.Add("Subject");
                //    ds.Tables[0].Columns.Add("Owner");
                //    ds.Tables[0].Columns.Add("IsNews");

                //    ds.Tables[0].Rows.Add(new object[]{
                //        belongSite,Environments.ActiveLanguage, GroupNewsId,Subject,Environments.CurrentLoginUser.LoginName,IsNews
                //    });

                //     string XmlData = ds.GetXml();
                //     Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //     for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //     {
                //         string RetValue = WServices[iIndexService].AddGroupNews(Environments.SecurityCode, XmlData);
                //         if (RetValue.Trim().Length > 0)
                //         {
                //             throw new Exception("Webservice:" + RetValue);
                //         }
                //     }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return GroupNewsId;
        }

        public string AddGroupNewsItem(
            SqlTransaction sqlTransaction,
            string GroupNewsId,
            string GroupNewsItemName,
            string Description,
            string ParentGroupNewsItemId,
            string RssUrl,
            string JumpUrl,
            int IndexOrder,
            int ShowInMenu,
            int LevelIndex,
            int IsGroupNews,
            string GroupNewsItemAlias,
            string ShortSubject,
            int AllowRss)
        {
            GroupNewsCls GroupNewsCls = new GroupNewsBLL().CreateModel(GroupNewsId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string GroupNewsItemId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableGroupNewsItem(GroupNewsItemId,belongSite,GroupNewsItemName,[" + Environments.ActiveLanguage + "GroupNewsItemName],fk_ParentGroupNewsItem_Id,IndexOrder,fk_GroupNews_Id,ShortGroupNewsItemId,RssUrl,JumpUrl,ShowInMenu, [" + Environments.ActiveLanguage + "gDescription],LevelIndex,IsGroupNews,Visible, GroupNewsItemAlias, ShortSubject, AllowRss) values (@GroupNewsItemId,@belongSite, @GroupNewsItemName,@GroupNewsItemName,@fk_ParentGroupNewsItem_Id,@IndexOrder,@fk_GroupNews_Id,@ShortGroupNewsItemId,@RssUrl,@JumpUrl,@ShowInMenu, @gDescription,@LevelIndex,@IsGroupNews,1,@GroupNewsItemAlias, @ShortSubject, @AllowRss)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = GroupNewsCls.belongSite;

            sqlCommand.Parameters.Add("@gDescription", SqlDbType.NVarChar);
            sqlCommand.Parameters["@gDescription"].Value = Description;

            sqlCommand.Parameters.Add("@GroupNewsItemName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@GroupNewsItemName"].Value = GroupNewsItemName;

            sqlCommand.Parameters.Add("@fk_ParentGroupNewsItem_Id", SqlDbType.NVarChar);
            if (ParentGroupNewsItemId != null)
            {
                sqlCommand.Parameters["@fk_ParentGroupNewsItem_Id"].Value = ParentGroupNewsItemId;
            }
            else
            {
                sqlCommand.Parameters["@fk_ParentGroupNewsItem_Id"].Value = DBNull.Value;
            }

            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            sqlCommand.Parameters["@IndexOrder"].Value = IndexOrder;

            sqlCommand.Parameters.Add("@LevelIndex", SqlDbType.Int);
            sqlCommand.Parameters["@LevelIndex"].Value = LevelIndex;

            sqlCommand.Parameters.Add("@GroupNewsItemAlias", SqlDbType.NVarChar);
            sqlCommand.Parameters["@GroupNewsItemAlias"].Value = GroupNewsItemAlias;


            sqlCommand.Parameters.Add("@fk_GroupNews_Id", SqlDbType.VarChar);
            sqlCommand.Parameters["@fk_GroupNews_Id"].Value = GroupNewsId;

            sqlCommand.Parameters.Add("@ShortGroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@ShortGroupNewsItemId"].Value = GroupNewsItemId.Substring(0, 8);


            sqlCommand.Parameters.Add("@RssUrl", SqlDbType.VarChar);
            sqlCommand.Parameters["@RssUrl"].Value = RssUrl;

            sqlCommand.Parameters.Add("@JumpUrl", SqlDbType.NVarChar);
            sqlCommand.Parameters["@JumpUrl"].Value = JumpUrl;

            sqlCommand.Parameters.Add("@ShowInMenu", SqlDbType.Int);
            sqlCommand.Parameters["@ShowInMenu"].Value = ShowInMenu;

            sqlCommand.Parameters.Add("@IsGroupNews", SqlDbType.Int);
            sqlCommand.Parameters["@IsGroupNews"].Value = IsGroupNews;

            sqlCommand.Parameters.Add("@ShortSubject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@ShortSubject"].Value = ShortSubject;

            sqlCommand.Parameters.Add("@AllowRss", SqlDbType.Int);
            sqlCommand.Parameters["@AllowRss"].Value = AllowRss;



            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return GroupNewsItemId;
        }


        public string SaveGroupNewsItem(
            SqlTransaction sqlTransaction, 
            string ActiveLanguage, 
            string GroupNewsItemId, 
            string GroupNewsItemName, 
            string Description, 
            int IndexOrder, 
            string RssUrl, 
            string JumpUrl, 
            int ShowInMenu, 
            int IsGroupNews, 
            int Visible, 
            string GroupNewsItemAlias, 
            string ShortSubject,
            int AllowRss,
            int  ShowInSubMenu)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = " Update TableGroupNewsItem set AllowRss=@AllowRss, ShortSubject=@ShortSubject, [" + ActiveLanguage + "GroupNewsItemName]=@GroupNewsItemName,[" + ActiveLanguage + "gDescription]=@gDescription, IndexOrder=@IndexOrder,RssUrl=@RssUrl,JumpUrl=@JumpUrl,ShowInMenu=@ShowInMenu,ShowInSubMenu=@ShowInSubMenu,IsGroupNews=@IsGroupNews, [Visible]=@Visible,GroupNewsItemAlias=@GroupNewsItemAlias where GroupNewsItemId=@GroupNewsItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            sqlCommand.Parameters.Add("@GroupNewsItemName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@GroupNewsItemName"].Value = GroupNewsItemName;

            sqlCommand.Parameters.Add("@gDescription", SqlDbType.NText);
            sqlCommand.Parameters["@gDescription"].Value = Description;

            sqlCommand.Parameters.Add("@RssUrl", SqlDbType.NVarChar);
            sqlCommand.Parameters["@RssUrl"].Value = RssUrl;

            sqlCommand.Parameters.Add("@JumpUrl", SqlDbType.NVarChar);
            sqlCommand.Parameters["@JumpUrl"].Value = JumpUrl;

            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            sqlCommand.Parameters["@IndexOrder"].Value = IndexOrder;

            sqlCommand.Parameters.Add("@ShowInMenu", SqlDbType.Int);
            sqlCommand.Parameters["@ShowInMenu"].Value = ShowInMenu;

            sqlCommand.Parameters.Add("@IsGroupNews", SqlDbType.Int);
            sqlCommand.Parameters["@IsGroupNews"].Value = IsGroupNews;

            sqlCommand.Parameters.Add("@Visible", SqlDbType.Int);
            sqlCommand.Parameters["@Visible"].Value = Visible;

            sqlCommand.Parameters.Add("@AllowRss", SqlDbType.Int);
            sqlCommand.Parameters["@AllowRss"].Value = AllowRss;

            sqlCommand.Parameters.Add("@ShowInSubMenu", SqlDbType.Int);
            sqlCommand.Parameters["@ShowInSubMenu"].Value = ShowInSubMenu;            

            sqlCommand.Parameters.Add("@GroupNewsItemAlias", SqlDbType.NVarChar);
            sqlCommand.Parameters["@GroupNewsItemAlias"].Value = GroupNewsItemAlias;

            sqlCommand.Parameters.Add("@ShortSubject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@ShortSubject"].Value = ShortSubject;

            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return GroupNewsItemId;
        }



        public void UpdateGroupNews(
            SqlTransaction sqlTransaction,
            string GroupNewsId,
            string Subject,
            int IsNews)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableGroupNews set Subject=@Subject,[" + Environments.ActiveLanguage + "Subject]=@Subject,IsNews=@IsNews where GroupNewsId=@GroupNewsId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@IsNews", SqlDbType.Int);
            sqlCommand.Parameters["@IsNews"].Value = IsNews;


            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }



        public void DeleteGroupNews(SqlTransaction sqlTransaction, string GroupNewsId)
        {
            GroupNewsCls oGroupNewsCls = new GroupNewsBLL().CreateModel(GroupNewsId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            Environment.Permission.DeletePermission(sqlTransaction, GroupNewsId);
            string Query =
                            " Delete from TableNews where fk_GroupNews_Id=@GroupNewsId" +
                            " Delete from TableGroupNews where GroupNewsId=@GroupNewsId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (oGroupNewsCls != null)
                {
                    SubjectDeleted = oGroupNewsCls.Subject;
                }
                Environments.AddDeletedRecordForSync(sqlTransaction, "TableGroupNews","Nhóm tin tức / menu", GroupNewsId,SubjectDeleted);
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void DeleteGroupNewsItem(SqlTransaction sqlTransaction, string GroupNewsItemId)
        {
            GroupNewsItemCls oGroupNewsItemCls = new GroupNewsBLL().CreateModelItem(Environments.ActiveLanguage, GroupNewsItemId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query =
                        " Delete from TableListNewsByGroupItem where fk_GroupNewsItem_Id in (select GroupNewsItemId from TableGroupNewsItem where GroupNewsItemId=@GroupNewsItemId);" +
                        " Delete from TableGroupNewsItem where fk_ParentGroupNewsItem_Id=@GroupNewsItemId;" +
                        " Delete from TableGroupNewsItem where GroupNewsItemId=@GroupNewsItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (oGroupNewsItemCls != null)
                {
                    SubjectDeleted = oGroupNewsItemCls.GroupNewsItemName;
                }
                Environments.AddDeletedRecordForSync(sqlTransaction, "TableGroupNewsItem","Nhóm tin chi tiết", GroupNewsItemId, SubjectDeleted);
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void PublicGroupNewsItemRoot(string GroupNewsId, string GroupNewsItemId)
        {
            Environments.CheckFrontEndDatabase();

            //DataSet ds = new DataSet();
            //string Query = " select * from TableGroupNews where GroupNewsId=@GroupNewsId;" +
            //               " select * from TableGroupNewsItem where GroupNewsItemId=@GroupNewsItemId;";
            //SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            //sqlConnection.Open();
            //SqlCommand sqlCommand = sqlConnection.CreateCommand();
            //sqlCommand.CommandText = Query;
            //sqlCommand.Parameters.Clear();

            //sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            //sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            //sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            //sqlCommand.Parameters["@GroupNewsId"].Value  = GroupNewsId;
            //SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            //sqlda.Fill(ds);
            //ds.Tables[0].TableName = "TableGroupNews";
            //ds.Tables[1].TableName = "TableGroupNewsItem";
            //string XmlData = ds.GetXml();
            //Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
            //for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
            //{
            //    string RetValue = WServices[iIndexService].PublishingGroupNewsItemRoot(Environments.SecurityCode, XmlData);
            //    if (RetValue.IndexOf("ERROR") != -1)
            //    {
            //        throw new Exception(RetValue);
            //    }
            //}
        }


        public void PublicGroupNewsItemSub(string GroupNewsId, string GroupNewsItemId, string SubGroupNewsItemId)
        {
            Environments.CheckFrontEndDatabase();

            //DataSet ds = new DataSet();
            //string Query = " select * from TableGroupNews where GroupNewsId=@GroupNewsId;" +
            //               " select * from TableGroupNewsItem where GroupNewsItemId=@GroupNewsItemId;"+
            //               " select * from TableGroupNewsItem where GroupNewsItemId=@SubGroupNewsItemId;";
            //SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            //sqlConnection.Open();
            //SqlCommand sqlCommand = sqlConnection.CreateCommand();
            //sqlCommand.CommandText = Query;
            //sqlCommand.Parameters.Clear();

            //sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            //sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            //sqlCommand.Parameters.Add("@SubGroupNewsItemId", SqlDbType.VarChar);
            //sqlCommand.Parameters["@SubGroupNewsItemId"].Value = SubGroupNewsItemId;

            //sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            //sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;

            //SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            //sqlda.Fill(ds);
            //ds.Tables[0].TableName = "TableGroupNews";
            //ds.Tables[1].TableName = "TableGroupNewsItem";
            //ds.Tables[2].TableName = "TableSubGroupNewsItem";
            //string XmlData = ds.GetXml();
            // Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
            // for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
            // {
            //     string RetValue = WServices[iIndexService].PublishingGroupNewsItemSub(Environments.SecurityCode, XmlData);
            //     if (RetValue.IndexOf("ERROR") != -1)
            //     {
            //         throw new Exception(RetValue);
            //     }
            // }
        }


        public void SaveGroupNewsHtml(
            SqlTransaction sqlTransaction,
            string GroupNewsItemId,
            string GroupNewsItemShortContent,
            string HtmlContent)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableGroupNewsItem set GroupNewsItemShortContent=@GroupNewsItemShortContent, HtmlContent=@HtmlContent where GroupNewsItemId=@GroupNewsItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            sqlCommand.Parameters.Add("@GroupNewsItemShortContent", SqlDbType.NVarChar);
            sqlCommand.Parameters["@GroupNewsItemShortContent"].Value = GroupNewsItemShortContent;


            sqlCommand.Parameters.Add("@HtmlContent", SqlDbType.NText);
            sqlCommand.Parameters["@HtmlContent"].Value = HtmlContent;


            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void UpdateLevelIndex(string GroupNewsId)
        {
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            int LevelIndex = 0;
            DataTable dtGroupNewsItem = ReadingGroupNewsItems(GroupNewsId, null);
            for (int iIndex = 0; iIndex < dtGroupNewsItem.Rows.Count; iIndex++)
            {
                string GroupNewsItemId = (string)dtGroupNewsItem.Rows[iIndex]["GroupNewsItemId"];

                sqlCommand.CommandText = "Update TableGroupNewsItem set LevelIndex=@LevelIndex where GroupNewsItemId=@GroupNewsItemId";
                sqlCommand.Parameters.Clear();

                sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
                sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

                sqlCommand.Parameters.Add("@LevelIndex", SqlDbType.Int);
                sqlCommand.Parameters["@LevelIndex"].Value = LevelIndex;

                sqlCommand.ExecuteNonQuery();

                LevelIndex++;
                UpdateSubLevelIndex(GroupNewsId, GroupNewsItemId, ref LevelIndex);
                LevelIndex--;
            }
        }

        void UpdateSubLevelIndex(string GroupNewsId, string ParentGroupNewsItemId, ref int LevelIndex)
        {
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();

            DataTable dtGroupNewsItem = ReadingGroupNewsItems(GroupNewsId, ParentGroupNewsItemId);
            for (int iIndex = 0; iIndex < dtGroupNewsItem.Rows.Count; iIndex++)
            {
                string GroupNewsItemId = (string)dtGroupNewsItem.Rows[iIndex]["GroupNewsItemId"];
                sqlCommand.CommandText = "Update TableGroupNewsItem set LevelIndex=@LevelIndex where GroupNewsItemId=@GroupNewsItemId";
                sqlCommand.Parameters.Clear();

                sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
                sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

                sqlCommand.Parameters.Add("@LevelIndex", SqlDbType.Int);
                sqlCommand.Parameters["@LevelIndex"].Value = LevelIndex;

                sqlCommand.ExecuteNonQuery();
                LevelIndex++;
                UpdateSubLevelIndex(GroupNewsId, GroupNewsItemId, ref LevelIndex);
                LevelIndex--;
            }
        }

        public void UpdateImage(string GroupNewsItemId, string ImageFile)
        {
            string ReadFile = new System.IO.FileInfo(ImageFile).Name;
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = " Update TableGroupNewsItem set GroupNewsItemImage=@GroupNewsItemImage where GroupNewsItemId=@GroupNewsItemId";
            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            sqlCommand.Parameters.Add("@GroupNewsItemImage", SqlDbType.NVarChar);
            sqlCommand.Parameters["@GroupNewsItemImage"].Value = ReadFile;

            sqlCommand.ExecuteNonQuery();
        }




        /// <summary>
        /// Sync nhóm tin lên máy chủ (GroupNewsItem)
        /// </summary>
        /// <param name="GroupNewsItemId"></param>
        public void SyncGroupNewsItemWithServer(
            string GroupNewsItemId,
            bool SyncWithNews)
        {
            GroupNewsItemCls GroupNewsItemCls = new GroupNewsBLL().CreateModelItem(Environments.ActiveLanguage, GroupNewsItemId);
            GroupNewsCls GroupNewsCls = new GroupNewsBLL().CreateModel(GroupNewsItemCls.GroupNewsId);

            bool UseWsForDeploy = Environments.UseWsForDeploy;
            DataSet ds = new DataSet();
            string Query = "";
            if (UseWsForDeploy)
            {
                Query =
                    " select * from TableGroupNewsItem where GroupNewsItemId=@GroupNewsItemId";
                SqlConnection sqlConnectionCheck = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnectionCheck.Open();
                SqlCommand sqlCommandCheck = sqlConnectionCheck.CreateCommand();
                sqlCommandCheck.CommandText = Query;
                sqlCommandCheck.Parameters.Clear();
                sqlCommandCheck.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
                sqlCommandCheck.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommandCheck);
                sqlda.Fill(ds);

                ds.Tables[0].TableName = "TableGroupNewsItem";

                byte[] oBytes1 = null;
                string GroupNewsItemImage = "";
                try
                {
                    GroupNewsItemImage = (string)ds.Tables["TableGroupNewsItem"].Rows[0]["GroupNewsItemImage"];
                }
                catch { }
                if (GroupNewsItemImage.Trim().Length > 0)
                {
                    string LoadFileImage =
                        Portal.Environment.Environments.WorkingPath + "\\Media\\" + Portal.Environment.Environments.PortalAlias + "\\GroupNews\\Images\\" + GroupNewsItemCls.GroupNewsId + "\\" + GroupNewsCls.belongSite + "\\" + GroupNewsItemImage;
                    if (new System.IO.FileInfo(LoadFileImage).Exists)
                    {
                        oBytes1 = Portal.Environment.Environments.GetBytesFromFile(LoadFileImage);
                    }
                }

                byte[] oBytes2 = null;
                string GroupNewsItemLargeImage = "";
                try
                {
                    GroupNewsItemLargeImage = (string)ds.Tables["TableGroupNewsItem"].Rows[0]["GroupNewsItemLargeImage"];
                }
                catch { }
                if (GroupNewsItemImage.Trim().Length > 0)
                {
                    string LoadFileLargeImage =
                        Portal.Environment.Environments.WorkingPath + "\\Media\\" + Portal.Environment.Environments.PortalAlias + "\\GroupNews\\Images\\" + GroupNewsItemCls.GroupNewsId + "\\" + GroupNewsCls.belongSite + "\\" + GroupNewsItemLargeImage;
                    if (new System.IO.FileInfo(LoadFileLargeImage).Exists)
                    {
                        oBytes1 = Portal.Environment.Environments.GetBytesFromFile(LoadFileLargeImage);
                    }
                }

                string XmlData = ds.GetXml();
                Framework.AppWs.WsReturnParamCls oWsReturnParamCls = Portal.Environment.Environments.CreateAppWs().SyncData(
                    Portal.Environment.Environments.WsSecurityCode,
                    GroupNewsItemId,
                    GroupNewsCls.belongSite,
                    XmlData,
                    oBytes1,
                    oBytes2,
                    null,
                    Framework.AppWs.ESycDataType.GroupNewsItem);
                if (oWsReturnParamCls.Error) throw new Exception(oWsReturnParamCls.ErrorMsg);
                if (SyncWithNews)
                {
                    DataTable dtNews = ReadingNewsForSync(GroupNewsItemId);
                    for (int iIndex = 0; iIndex < dtNews.Rows.Count; iIndex++)
                    {
                        string NewsId = (string)dtNews.Rows[iIndex]["NewsId"];
                        new NewsBLL().SyncNews(NewsId);
                    }
                }
            }
        }


        
    }
}
